select *
from sqlru_book.Customers
where 
	city = 'San Jose'
	and rating >200;
    
select *
from sqlru_book.Customers
where 
	city = 'San Jose'
	or rating >200;
    
select *
from sqlru_book.Customers
where 
	city = 'San Jose'
	or not rating > 200;
    
select *
from sqlru_book.Customers
where not 
	city = 'San Jose'
	or rating > 200;
    
select *
from sqlru_book.Customers
where not
	(city = 'San Jose'
	or rating >200);
    
select *
from sqlru_book.Orders
where (onum > 3001 and cnum > 2003 );

/* 	onum	amt	     odate	    cnum	snum
	3008	4723	10/05/1990	2006	1001
	3011	9891.88	10/06/1990	2006	1001
	3007	75.75	10/04/1990	2004	1002
	3010	1309.95	10/06/1990	2004	1002
	3002	1900.1	10/03/1990	2007	1004
	3006	1098.16	10/03/1990	2008	1007
					 */
				

select *
from sqlru_book.Orders
where (onum > 3001 and cnum > 2003 ) or snum = 1003 ;

/* 3002	1900.1	10/03/1990	2007	1004
3006	1098.16	10/03/1990	2008	1007
3007	75.75	10/04/1990	2004	1002
3008	4723	10/05/1990	2006	1001
3009	1713.23	10/04/1990	2002	1003
3010	1309.95	10/06/1990	2004	1002
3011	9891.88	10/06/1990	2006	1001 */

/* Ответы */
/* 1. Напишите запрос который может дать вам все порядки со значениями суммы выше чем $1,000. */

select * 
from 	sqlru_book.orders
where amt >1000;

/* 2. Напишите запрос который может выдать вам поля sname и city для всех продавцов в Лондоне с комиссионными выше .10 . */

select sname , city 
from sqlru_book.Salespeople
where city = 'London'
	and comm > .1;
    
/* 3. Напишите запрос к таблице Заказчиков чей вывод может включить всех заказчиков с оценкой=< 100, если они не находятся в Риме */

select *
from sqlru_book.Customers 
where rating <= 100 
	and not city = 'Rome' ;
	
/* 4. Что может быть выведено в результате следующего запроса? */

SELECT *
FROM sqlru_book.Orders
WHERE (amt < 1000 OR
NOT (odate='10/03/1990'
AND cnum > 2003 ));

/* handle:
            Orders
   ----------------------------------------------- 
    onum  |    amt    |    odate    | cnum | snum  
   -------|-----------|-------------|------|-----  {1}{2}{3}{4}{5}{6}
    3001  |    18.69  |  10/03/1990 | 2008 | 1007   +  +  +  +     +
    3003  |   767.19  |  10/03/1990 | 2001 | 1001   +  +        +  +
    3002  |  1900.10  |  10/03/1990 | 2007 | 1004      +  +  +
    3005  |  5160.45  |  10/03/1990 | 2003 | 1002      +        +  +
    3006  |  1098.16  |  10/03/1990 | 2008 | 1007      +  +  +
    3009  |  1713.23  |  10/04/1990 | 2002 | 1003               +  +
    3007  |    75.75  |  10/04/1990 | 2004 | 1002   +     +     +  +
    3008  |  4723.00  |  10/05/1990 | 2006 | 1001         +     +  +
    3010  |  1309.95  |  10/06/1990 | 2004 | 1002         +     +  +
    3011  |  9891.88  |  10/06/1990 | 2006 | 1001         +     +  +
	
	{1}: amt < 1000
	{2}: odate=10/03/1990
	{3}: cnum > 2003
	{4}: {2} and {3}
	{5}: not {4}
	{6}: {1} or {5}
   ----------------------------------------------- 
   
   calculate:
3001	18.69	10/03/1990	2008	1007
3003	767.19	10/03/1990	2001	1001
3005	5160.45	10/03/1990	2003	1002
3007	75.75	10/04/1990	2004	1002
3008	4723	10/05/1990	2006	1001
3009	1713.23	10/04/1990	2002	1003
3010	1309.95	10/06/1990	2004	1002
3011	9891.88	10/06/1990	2006	1001
				
    */

/* 5. Что может быть выведено в результате следующего запроса? */
    
SELECT *
FROM sqlru_book.Orders
WHERE NOT ((odate='10/03/1990' OR snum > 1006)
AND amt >= 1500 );

/* handle:
            Orders
   ----------------------------------------------- 
    onum  |    amt    |    odate    | cnum | snum  
   -------|-----------|-------------|------|-----  {1}{2}{3}{4}{5}{6}
    3001  |    18.69  |  10/03/1990 | 2008 | 1007      +  +  +     +
    3003  |   767.19  |  10/03/1990 | 2001 | 1001      +     +     +
    3002  |  1900.10  |  10/03/1990 | 2007 | 1004   +  +     +  +
    3005  |  5160.45  |  10/03/1990 | 2003 | 1002   +  +     +  +
    3006  |  1098.16  |  10/03/1990 | 2008 | 1007      +  +  +     +
    3009  |  1713.23  |  10/04/1990 | 2002 | 1003   +              +
    3007  |    75.75  |  10/04/1990 | 2004 | 1002                  +
    3008  |  4723.00  |  10/05/1990 | 2006 | 1001   +              +
    3010  |  1309.95  |  10/06/1990 | 2004 | 1002                  +
    3011  |  9891.88  |  10/06/1990 | 2006 | 1001   +              +
	
	{1}: amt >=1500
	{2}: odate=10/03/1990
	{3}: snum > 1006
	{4}: {2} or {3}
	{5}: {1} and {4}
	{6}: not {5}
   ----------------------------------------------- 
   
   calculate:
3001	18.69	10/03/1990	2008	1007
3003	767.19	10/03/1990	2001	1001
3006	1098.16	10/03/1990	2008	1007
3007	75.75	10/04/1990	2004	1002
3008	4723	10/05/1990	2006	1001
3009	1713.23	10/04/1990	2002	1003
3010	1309.95	10/06/1990	2004	1002
3011	9891.88	10/06/1990	2006	1001
				
*/

/* 6. Как можно проще переписать такой запрос? */

SELECT snum, sname, city, comm
FROM sqlru_book.Salespeople
WHERE (comm > .12 OR
comm < .14 );

/* answer : */

select *
	from sqlru_book.salespeople
    


